/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Models;

import java.util.Calendar;
import java.sql.*;
import java.util.Date;


import java.text.*;
import java.util.Vector;

/**
 *
 * @author Venus
 */
public class ThongKe_Model {

    public static boolean CheckDate(String ngaybd, String ngaykt) {
        try {
            SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
            Calendar c1 = Calendar.getInstance();
            Calendar c2 = Calendar.getInstance();
            Date date1 = fm.parse(ngaybd);
            Date date2 = fm.parse(ngaykt);
            c1.setTime(date1);
            c2.setTime(date2);
            if (c1.after(c2)) {
                return false;
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public static Vector Turnoverfood(String ngaybd, String ngaykt, String manhomma, String tenmonan, int langid, String sl) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select A.MaMA, sum(A.SoLuong), sum(A.ThanhTien), A.Gia  "
                    + " from chitiethd A, hoadon B, monan C "
                    + " where A.SoHD = B.SoHD and A.MaMA = C.MaMA and C.MaNN = " + langid + " ";
            String[] arr = {manhomma, tenmonan};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(manhomma)) {
                    query = query + " and MaNMA = " + arr[i] + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tenmonan)) {
                    query = query + " and TenMA like '%" + arr[i] + "%' ";
                }
            }
            if (!ngaybd.equals("")) {
                String rsdate = "";
                SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
                Calendar c1 = Calendar.getInstance();
                Calendar c2 = Calendar.getInstance();
                Calendar c3 = Calendar.getInstance();
                Date date1 = fm.parse(ngaybd);
                Date date2 = fm.parse(ngaykt);
                c1.setTime(date1);
                c2.setTime(date2);
                String query1 = " select GioRa from hoadon ";
                rss = stmt.executeQuery(query1);
                while (rss.next()) {
                    String x = rss.getString(1);
                    String ngaygio[] = x.split(" ");
                    String y = ngaygio[0];
                    Date date3 = fm.parse(y);
                    c3.setTime(date3);
                    if (c1.before(c2)) {
                        if (c1.before(c3) && c2.after(c3)) {
                            rsdate = rsdate + y + ";";
                        }
                        if (c3.equals(c1)) {
                            rsdate = rsdate + y + ";";
                        }
                        if (c3.equals(c2)) {
                            rsdate = rsdate + y + ";";
                        }
                    }
                    if (c1.equals(c2)) {
                        if (c3.equals(c1)) {
                            rsdate = rsdate + y + ";";
                        }
                    }
                }
                String dateqry[] = rsdate.split(";");
                if (dateqry.length > 1) {
                    query = query + " and ( GioRa like '%" + dateqry[0] + "%'  ";
                    for (int j = 0; j < dateqry.length; j++) {
                        query = query + " or GioRa like '%" + dateqry[j] + "%'  ";
                    }
                    query = query + " ) ";
                } else {
                    query = query + " and  GioRa like '%" + dateqry[0] + "%'  ";
                }
            }
            query = query + " group by A.MaMA ";
            query = query + " Order by sum(A.ThanhTien) desc ";
            if (!sl.equals("")) {
                query = query + "limit " + sl;
            }
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.CTHoaDon cthd = new Beans.CTHoaDon();
                cthd.setMaMA(rss.getInt(1));
                cthd.setSoLuong(rss.getInt(2));
                cthd.setThanhTien(rss.getDouble(3));
                cthd.setGia(rss.getDouble(4));
                v.add(cthd);
            }
            stmt.close();
            rss.close();

            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static Vector Turnoverfoodgr(String ngaybd, String ngaykt, String manhomma, String tenmonan, int langid, String sl) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select MaNMA, sum(A.ThanhTien)  "
                    + " from chitiethd A, hoadon B, monan C "
                    + " where A.SoHD = B.SoHD and A.MaMA = C.MaMA and C.MaNN = " + langid + " ";

            if (!ngaybd.equals("")) {
                SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
                String rsdate = "";                                
                Calendar c1 = Calendar.getInstance();
                Calendar c2 = Calendar.getInstance();
                Calendar c3 = Calendar.getInstance();
                Date date1 = fm.parse(ngaybd);
                Date date2 = fm.parse(ngaykt);
                c1.setTime(date1);
                c2.setTime(date2);
                String query1 = " select GioRa from hoadon ";
                rss = stmt.executeQuery(query1);
                while (rss.next()) {
                    String x = rss.getString(1);
                    String ngaygio[] = x.split(" ");
                    String y = ngaygio[0];
                    Date date3 = fm.parse(y);
                    c3.setTime(date3);
                    if (c1.before(c2)) {
                        if (c1.before(c3) && c2.after(c3)) {
                            rsdate = rsdate + y + ";";
                        }
                        if (c3.equals(c1)) {
                            rsdate = rsdate + y + ";";
                        }
                        if (c3.equals(c2)) {
                            rsdate = rsdate + y + ";";
                        }
                    }
                    if (c1.equals(c2)) {
                        if (c3.equals(c1)) {
                            rsdate = rsdate + y + ";";
                        }
                    }
                }
                String dateqry[] = rsdate.split(";");
                if (dateqry.length > 1) {
                    query = query + " and ( GioRa like '%" + dateqry[0] + "%'  ";
                    for (int j = 0; j < dateqry.length; j++) {
                        query = query + " or GioRa like '%" + dateqry[j] + "%'  ";
                    }
                    query = query + " ) ";
                } else {
                    query = query + " and  GioRa like '%" + dateqry[0] + "%'  ";
                }
            }
            query = query + " group by MaNMA ";
            query = query + " Order by sum(A.ThanhTien) desc ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.MonAn ma = new Beans.MonAn();
                ma.setMaNMA(rss.getInt(1));
                v.add(ma);
            }
            stmt.close();
            rss.close();

            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static Vector Turnoverfoodgroup(String ngaybd, String ngaykt, int langid, int MaNMA) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select A.MaMA, sum(A.SoLuong), sum(A.ThanhTien), A.Gia  "
                    + " from chitiethd A, hoadon B, monan C "
                    + " where A.SoHD = B.SoHD and A.MaMA = C.MaMA and C.MaNN = " + langid + " and C.MaNMA = " + MaNMA + " ";
            if (!ngaybd.equals("")) {
                String rsdate = "";
                SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
                Calendar c1 = Calendar.getInstance();
                Calendar c2 = Calendar.getInstance();
                Calendar c3 = Calendar.getInstance();
                Date date1 = fm.parse(ngaybd);
                Date date2 = fm.parse(ngaykt);
                c1.setTime(date1);
                c2.setTime(date2);
                String query1 = " select GioRa from hoadon ";
                rss = stmt.executeQuery(query1);
                while (rss.next()) {
                    String x = rss.getString(1);
                    String ngaygio[] = x.split(" ");
                    String y = ngaygio[0];
                    Date date3 = fm.parse(y);
                    c3.setTime(date3);
                    if (c1.before(c2)) {
                        if (c1.before(c3) && c2.after(c3)) {
                            rsdate = rsdate + y + ";";
                        }
                        if (c3.equals(c1)) {
                            rsdate = rsdate + y + ";";
                        }
                        if (c3.equals(c2)) {
                            rsdate = rsdate + y + ";";
                        }
                    }
                    if (c1.equals(c2)) {
                        if (c3.equals(c1)) {
                            rsdate = rsdate + y + ";";
                        }
                    }
                }
                String dateqry[] = rsdate.split(";");
                if (dateqry.length > 1) {
                    query = query + " and ( GioRa like '%" + dateqry[0] + "%'  ";
                    for (int j = 0; j < dateqry.length; j++) {
                        query = query + " or GioRa like '%" + dateqry[j] + "%'  ";
                    }
                    query = query + " ) ";
                } else {
                    query = query + " and  GioRa like '%" + dateqry[0] + "%'  ";
                }
            }
            query = query + " group by A.MaMA ";
            query = query + " Order by sum(A.ThanhTien) desc ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.CTHoaDon cthd = new Beans.CTHoaDon();
                cthd.setMaMA(rss.getInt(1));
                cthd.setSoLuong(rss.getInt(2));
                cthd.setThanhTien(rss.getDouble(3));
                cthd.setGia(rss.getDouble(4));
                v.add(cthd);
            }
            stmt.close();
            rss.close();

            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static double DoanhThuMA(String ngaybd, String ngaykt, String manhomma, String tenmonan, int langid, String sl) {
        double doanhthu = 0;
        Connection conn = null;
        ResultSet rss = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select sum(A.ThanhTien) "
                    + " from chitiethd A, hoadon B, monan C "
                    + " where A.SoHD = B.SoHD and A.MaMA = C.MaMA and C.MaNN = " + langid + " ";
            String[] arr = {manhomma, tenmonan};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(manhomma)) {
                    query = query + " and MaNMA = " + arr[i] + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tenmonan)) {
                    query = query + " and TenMA like '%" + arr[i] + "%' ";
                }
            }
            if (!ngaybd.equals("")) {
                String rsdate = "";
                SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
                Calendar c1 = Calendar.getInstance();
                Calendar c2 = Calendar.getInstance();
                Calendar c3 = Calendar.getInstance();
                Date date1 = fm.parse(ngaybd);
                Date date2 = fm.parse(ngaykt);
                c1.setTime(date1);
                c2.setTime(date2);
                String query1 = " select GioRa from hoadon ";
                rss = stmt.executeQuery(query1);
                while (rss.next()) {
                    String x = rss.getString(1);
                    String ngaygio[] = x.split(" ");
                    String y = ngaygio[0];
                    Date date3 = fm.parse(y);
                    c3.setTime(date3);
                    if (c1.before(c2)) {
                        if (c1.before(c3) && c2.after(c3)) {
                            rsdate = rsdate + y + ";";
                        }
                        if (c3.equals(c1)) {
                            rsdate = rsdate + y + ";";
                        }
                        if (c3.equals(c2)) {
                            rsdate = rsdate + y + ";";
                        }
                    }
                    if (c1.equals(c2)) {
                        if (c3.equals(c1)) {
                            rsdate = rsdate + y + ";";
                        }
                    }
                }
                String dateqry[] = rsdate.split(";");
                if (dateqry.length > 1) {
                    query = query + " and ( GioRa like '%" + dateqry[0] + "%'  ";
                    for (int j = 0; j < dateqry.length; j++) {
                        query = query + " or GioRa like '%" + dateqry[j] + "%'  ";
                    }
                    query = query + " ) ";
                } else {
                    query = query + " and  GioRa like '%" + dateqry[0] + "%'  ";
                }
            }
            if (!sl.equals("")) {
                query = query + "limit " + sl;
            }
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                doanhthu = rss.getDouble(1);
            }
            stmt.close();
            rss.close();

            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return doanhthu;
    }

    public static Vector Turnoveruser1(String ngaybd, String ngaykt, String tennv, int langid, String soluong) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select A.MaNVDB,  sum(A.ThanhTien)  "
                    + " from chitiethd A, hoadon B, nhanvien C "
                    + " where A.SoHD = B.SoHD and A.MaNVDB = C.MaNV ";            
            if (!tennv.equals("")) {
                query = query + " and HoTen like '%" + tennv + "%' ";
            }
            if (!ngaybd.equals("")) {
                String rsdate = "";
                SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
                Calendar c1 = Calendar.getInstance();
                Calendar c2 = Calendar.getInstance();
                Calendar c3 = Calendar.getInstance();
                Date date1 = fm.parse(ngaybd);
                Date date2 = fm.parse(ngaykt);
                c1.setTime(date1);
                c2.setTime(date2);
                String query1 = " select GioRa from hoadon ";
                rss = stmt.executeQuery(query1);
                while (rss.next()) {
                    String x = rss.getString(1);
                    String ngaygio[] = x.split(" ");
                    String y = ngaygio[0];
                    Date date3 = fm.parse(y);
                    c3.setTime(date3);
                    if (c1.before(c2)) {
                        if (c1.before(c3) && c2.after(c3)) {
                            rsdate = rsdate + y + ";";
                        }
                        if (c3.equals(c1)) {
                            rsdate = rsdate + y + ";";
                        }
                        if (c3.equals(c2)) {
                            rsdate = rsdate + y + ";";
                        }
                    }
                    if (c1.equals(c2)) {
                        if (c3.equals(c1)) {
                            rsdate = rsdate + y + ";";
                        }
                    }
                }
                String dateqry[] = rsdate.split(";");
                if (dateqry.length > 1) {
                    query = query + " and ( GioRa like '%" + dateqry[0] + "%'  ";
                    for (int j = 0; j < dateqry.length; j++) {
                        query = query + " or GioRa like '%" + dateqry[j] + "%'  ";
                    }
                    query = query + " ) ";
                } else {
                    query = query + " and  GioRa like '%" + dateqry[0] + "%'  ";
                }
            }
            query = query + " group by A.MaNVDB ";
            query = query + " Order by sum(A.ThanhTien) desc ";
            if (!soluong.equals("")) {
                query = query + "limit " + soluong;
            }
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.CTHoaDon cthd = new Beans.CTHoaDon();
                cthd.setMaNVDB(rss.getInt(1));
                cthd.setThanhTien(rss.getDouble(2));
                v.add(cthd);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }
    public static Vector Turnoveruser2(String ngaybd, String ngaykt, String tennv, int langid, String soluong) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select MaNVPV,  sum(ThanhTien)  "
                    + " from hoadon A, nhanvien B "
                    + " where A.MaNVPV = B.MaNV ";
            if (!tennv.equals("")) {
                query = query + " and HoTen like '%" + tennv + "%' ";
            }
            if (!ngaybd.equals("")) {
                String rsdate = "";
                SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
                Calendar c1 = Calendar.getInstance();
                Calendar c2 = Calendar.getInstance();
                Calendar c3 = Calendar.getInstance();
                Date date1 = fm.parse(ngaybd);
                Date date2 = fm.parse(ngaykt);
                c1.setTime(date1);
                c2.setTime(date2);
                String query1 = " select GioRa from hoadon ";
                rss = stmt.executeQuery(query1);
                while (rss.next()) {
                    String x = rss.getString(1);
                    String ngaygio[] = x.split(" ");
                    String y = ngaygio[0];
                    Date date3 = fm.parse(y);
                    c3.setTime(date3);
                    if (c1.before(c2)) {
                        if (c1.before(c3) && c2.after(c3)) {
                            rsdate = rsdate + y + ";";
                        }
                        if (c3.equals(c1)) {
                            rsdate = rsdate + y + ";";
                        }
                        if (c3.equals(c2)) {
                            rsdate = rsdate + y + ";";
                        }
                    }
                    if (c1.equals(c2)) {
                        if (c3.equals(c1)) {
                            rsdate = rsdate + y + ";";
                        }
                    }
                }
                String dateqry[] = rsdate.split(";");
                if (dateqry.length > 1) {
                    query = query + " and ( GioRa like '%" + dateqry[0] + "%'  ";
                    for (int j = 0; j < dateqry.length; j++) {
                        query = query + " or GioRa like '%" + dateqry[j] + "%'  ";
                    }
                    query = query + " ) ";
                } else {
                    query = query + " and  GioRa like '%" + dateqry[0] + "%'  ";
                }
            }
            query = query + " group by MaNVPV ";
            query = query + " Order by sum(ThanhTien) desc ";
            if (!soluong.equals("")) {
                query = query + "limit " + soluong;
            }
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.CTHoaDon cthd = new Beans.CTHoaDon();
                cthd.setMaNVDB(rss.getInt(1));
                cthd.setThanhTien(rss.getDouble(2));
                v.add(cthd);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }
    public static Vector Turnoveruser3(String ngaybd, String ngaykt, String tennv, int langid, String soluong) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select MaNVTN,  sum(ThanhTien)  "
                    + " from hoadon A, nhanvien B "
                    + " where A.MaNVTN = B.MaNV ";
            if (!tennv.equals("")) {
                query = query + " and HoTen like '%" + tennv + "%' ";
            }
            if (!ngaybd.equals("")) {
                String rsdate = "";
                SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
                Calendar c1 = Calendar.getInstance();
                Calendar c2 = Calendar.getInstance();
                Calendar c3 = Calendar.getInstance();
                Date date1 = fm.parse(ngaybd);
                Date date2 = fm.parse(ngaykt);
                c1.setTime(date1);
                c2.setTime(date2);
                String query1 = " select GioRa from hoadon ";
                rss = stmt.executeQuery(query1);
                while (rss.next()) {
                    String x = rss.getString(1);
                    String ngaygio[] = x.split(" ");
                    String y = ngaygio[0];
                    Date date3 = fm.parse(y);
                    c3.setTime(date3);
                    if (c1.before(c2)) {
                        if (c1.before(c3) && c2.after(c3)) {
                            rsdate = rsdate + y + ";";
                        }
                        if (c3.equals(c1)) {
                            rsdate = rsdate + y + ";";
                        }
                        if (c3.equals(c2)) {
                            rsdate = rsdate + y + ";";
                        }
                    }
                    if (c1.equals(c2)) {
                        if (c3.equals(c1)) {
                            rsdate = rsdate + y + ";";
                        }
                    }
                }
                String dateqry[] = rsdate.split(";");
                if (dateqry.length > 1) {
                    query = query + " and ( GioRa like '%" + dateqry[0] + "%'  ";
                    for (int j = 0; j < dateqry.length; j++) {
                        query = query + " or GioRa like '%" + dateqry[j] + "%'  ";
                    }
                    query = query + " ) ";
                } else {
                    query = query + " and  GioRa like '%" + dateqry[0] + "%'  ";
                }
            }
            query = query + " group by MaNVTN ";
            query = query + " Order by sum(ThanhTien) desc ";
            if (!soluong.equals("")) {
                query = query + "limit " + soluong;
            }
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.CTHoaDon cthd = new Beans.CTHoaDon();
                cthd.setMaNVDB(rss.getInt(1));
                cthd.setThanhTien(rss.getDouble(2));
                v.add(cthd);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }
    public static double DoanhThuUser(String ngaybd, String ngaykt, String tennv, int langid, String soluong) {
        double doanhthu = 0;
        Connection conn = null;
        ResultSet rss = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select sum(A.ThanhTien) "
                    + " from chitiethd A, hoadon B, nhanvien C "
                    + " where A.SoHD = B.SoHD and A.MaNVDB = C.MaNV ";
            if (!tennv.equals("")) {
                query = query + " and HoTen like '%" + tennv + "%' ";
            }
            if (!ngaybd.equals("")) {
                String rsdate = "";
                SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
                Calendar c1 = Calendar.getInstance();
                Calendar c2 = Calendar.getInstance();
                Calendar c3 = Calendar.getInstance();
                Date date1 = fm.parse(ngaybd);
                Date date2 = fm.parse(ngaykt);
                c1.setTime(date1);
                c2.setTime(date2);
                String query1 = " select GioRa from hoadon ";
                rss = stmt.executeQuery(query1);
                while (rss.next()) {
                    String x = rss.getString(1);
                    String ngaygio[] = x.split(" ");
                    String y = ngaygio[0];
                    Date date3 = fm.parse(y);
                    c3.setTime(date3);
                    if (c1.before(c2)) {
                        if (c1.before(c3) && c2.after(c3)) {
                            rsdate = rsdate + y + ";";
                        }
                        if (c3.equals(c1)) {
                            rsdate = rsdate + y + ";";
                        }
                        if (c3.equals(c2)) {
                            rsdate = rsdate + y + ";";
                        }
                    }
                    if (c1.equals(c2)) {
                        if (c3.equals(c1)) {
                            rsdate = rsdate + y + ";";
                        }
                    }
                }
                String dateqry[] = rsdate.split(";");
                if (dateqry.length > 1) {
                    query = query + " and ( GioRa like '%" + dateqry[0] + "%'  ";
                    for (int j = 0; j < dateqry.length; j++) {
                        query = query + " or GioRa like '%" + dateqry[j] + "%'  ";
                    }
                    query = query + " ) ";
                } else {
                    query = query + " and  GioRa like '%" + dateqry[0] + "%'  ";
                }
            }
            if (!soluong.equals("")) {
                query = query + "limit " + soluong;
            }
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                doanhthu = rss.getDouble(1);
            }
            stmt.close();
            rss.close();

            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return doanhthu;
    }

    public static Vector Turnovercustomer(String ngaybd, String ngaykt, String tennv, int langid, String soluong) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;

        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select B.MaKH,  sum(B.TongCong)  "
                    + " from  hoadon B, khachhang C "
                    + " where  B.MaKH = C.MaKH ";
            if (!tennv.equals("")) {
                query = query + " and HoTen like '%" + tennv + "%' ";
            }
            if (!ngaybd.equals("")) {
                String rsdate = "";
                SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
                Calendar c1 = Calendar.getInstance();
                Calendar c2 = Calendar.getInstance();
                Calendar c3 = Calendar.getInstance();
                Date date1 = fm.parse(ngaybd);
                Date date2 = fm.parse(ngaykt);
                c1.setTime(date1);
                c2.setTime(date2);
                String query1 = " select GioRa from hoadon ";
                rss = stmt.executeQuery(query1);
                while (rss.next()) {
                    String x = rss.getString(1);
                    String ngaygio[] = x.split(" ");
                    String y = ngaygio[0];
                    Date date3 = fm.parse(y);
                    c3.setTime(date3);
                    if (c1.before(c2)) {
                        if (c1.before(c3) && c2.after(c3)) {
                            rsdate = rsdate + y + ";";
                        }
                        if (c3.equals(c1)) {
                            rsdate = rsdate + y + ";";
                        }
                        if (c3.equals(c2)) {
                            rsdate = rsdate + y + ";";
                        }
                    }
                    if (c1.equals(c2)) {
                        if (c3.equals(c1)) {
                            rsdate = rsdate + y + ";";
                        }
                    }
                }
                String dateqry[] = rsdate.split(";");
                if (dateqry.length > 1) {
                    query = query + " and ( GioRa like '%" + dateqry[0] + "%'  ";
                    for (int j = 0; j < dateqry.length; j++) {
                        query = query + " or GioRa like '%" + dateqry[j] + "%'  ";
                    }
                    query = query + " ) ";
                } else {
                    query = query + " and  GioRa like '%" + dateqry[0] + "%'  ";
                }
            }
            query = query + " group by B.MaKH ";
            query = query + " Order by sum(B.TongCong) desc ";
            if (!soluong.equals("")) {
                query = query + "limit " + soluong;
            }
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.HoaDon hd = new Beans.HoaDon();
                hd.setMaKH(rss.getInt(1));
                hd.setTongCong(rss.getDouble(2));
                v.add(hd);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static double DoanhThuKH(String ngaybd, String ngaykt, String tennv, int langid, String soluong) {
        double doanhthu = 0;
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select sum(B.TongCong)  "
                    + " from  hoadon B, khachhang C "
                    + " where  B.MaKH = C.MaKH ";
            if (!tennv.equals("")) {
                query = query + " and HoTen like '%" + tennv + "%' ";
            }
            if (!ngaybd.equals("")) {
                String rsdate = "";
                SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
                
                
                Calendar c1 = Calendar.getInstance();
                Calendar c2 = Calendar.getInstance();
                Calendar c3 = Calendar.getInstance();
                Date date1 = fm.parse(ngaybd);
                Date date2 = fm.parse(ngaykt);
                c1.setTime(date1);
                c2.setTime(date2);
                String query1 = " select GioRa from hoadon ";
                rss = stmt.executeQuery(query1);
                while (rss.next()) {
                    String x = rss.getString(1);
                    String ngaygio[] = x.split(" ");
                    String y = ngaygio[0];
                    Date date3 = fm.parse(y);
                    c3.setTime(date3);
                    if (c1.before(c2)) {
                        if (c1.before(c3) && c2.after(c3)) {
                            rsdate = rsdate + y + ";";
                        }
                        if (c3.equals(c1)) {
                            rsdate = rsdate + y + ";";
                        }
                        if (c3.equals(c2)) {
                            rsdate = rsdate + y + ";";
                        }
                    }
                    if (c1.equals(c2)) {
                        if (c3.equals(c1)) {
                            rsdate = rsdate + y + ";";
                        }
                    }
                }
                String dateqry[] = rsdate.split(";");
                if (dateqry.length > 1) {
                    query = query + " and ( GioRa like '%" + dateqry[0] + "%'  ";
                    for (int j = 0; j < dateqry.length; j++) {
                        query = query + " or GioRa like '%" + dateqry[j] + "%'  ";
                    }
                    query = query + " ) ";
                } else {
                    query = query + " and  GioRa like '%" + dateqry[0] + "%'  ";
                }
            }
            if (!soluong.equals("")) {
                query = query + "limit " + soluong;
            }
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                doanhthu = rss.getDouble(1);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return doanhthu;
    }

    public static Vector Turnoverarea(String ngaybd, String ngaykt, String makhuvuc, String tenban, int langid, String soluong) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select A.MaBan,  sum(A.TongCong)  "
                    + " from hoadon A, ban B "
                    + " where A.MaBan = B.MaBan ";
            String[] arr = {makhuvuc, tenban};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(makhuvuc)) {
                    query = query + " and MaKV = " + arr[i] + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tenban)) {
                    query = query + " and TenBan = " + arr[i] + " ";
                }
            }
            if (!ngaybd.equals("")) {
                String rsdate = "";
                SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
                Calendar c1 = Calendar.getInstance();
                Calendar c2 = Calendar.getInstance();
                Calendar c3 = Calendar.getInstance();
                Date date1 = fm.parse(ngaybd);
                Date date2 = fm.parse(ngaykt);
                c1.setTime(date1);
                c2.setTime(date2);
                String query1 = " select GioRa from hoadon ";
                rss = stmt.executeQuery(query1);
                while (rss.next()) {
                    String x = rss.getString(1);
                    String ngaygio[] = x.split(" ");
                    String y = ngaygio[0];
                    Date date3 = fm.parse(y);
                    c3.setTime(date3);
                    if (c1.before(c2)) {
                        if (c1.before(c3) && c2.after(c3)) {
                            rsdate = rsdate + y + ";";
                        }
                        if (c3.equals(c1)) {
                            rsdate = rsdate + y + ";";
                        }
                        if (c3.equals(c2)) {
                            rsdate = rsdate + y + ";";
                        }
                    }
                    if (c1.equals(c2)) {
                        if (c3.equals(c1)) {
                            rsdate = rsdate + y + ";";
                        }
                    }
                }
                String dateqry[] = rsdate.split(";");
                if (dateqry.length > 1) {
                    query = query + " and ( GioRa like '%" + dateqry[0] + "%'  ";
                    for (int j = 0; j < dateqry.length; j++) {
                        query = query + " or GioRa like '%" + dateqry[j] + "%'  ";
                    }
                    query = query + " ) ";
                } else {
                    query = query + " and  GioRa like '%" + dateqry[0] + "%'  ";
                }
            }
            query = query + " group by A.MaBan ";
            query = query + " Order by sum(A.TongCong) desc ";
            if (!soluong.equals("")) {
                query = query + "limit " + soluong;
            }
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.HoaDon hd = new Beans.HoaDon();
                hd.setMaBan(rss.getInt(1));
                hd.setTongCong(rss.getDouble(2));
                v.add(hd);
            }
            stmt.close();
            rss.close();

            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static Vector Turnoverareagroup(String ngaybd, String ngaykt, int langid, int MaKV) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select A.MaBan,  sum(A.TongCong)  "
                    + " from hoadon A, ban B "
                    + " where A.MaBan = B.MaBan and MaKV = " + MaKV + " ";

            if (!ngaybd.equals("")) {
                String rsdate = "";
                SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
                Calendar c1 = Calendar.getInstance();
                Calendar c2 = Calendar.getInstance();
                Calendar c3 = Calendar.getInstance();
                Date date1 = fm.parse(ngaybd);
                Date date2 = fm.parse(ngaykt);
                c1.setTime(date1);
                c2.setTime(date2);
                String query1 = " select GioRa from hoadon ";
                rss = stmt.executeQuery(query1);
                while (rss.next()) {
                    String x = rss.getString(1);
                    String ngaygio[] = x.split(" ");
                    String y = ngaygio[0];
                    Date date3 = fm.parse(y);
                    c3.setTime(date3);
                    if (c1.before(c2)) {
                        if (c1.before(c3) && c2.after(c3)) {
                            rsdate = rsdate + y + ";";
                        }
                        if (c3.equals(c1)) {
                            rsdate = rsdate + y + ";";
                        }
                        if (c3.equals(c2)) {
                            rsdate = rsdate + y + ";";
                        }
                    }
                    if (c1.equals(c2)) {
                        if (c3.equals(c1)) {
                            rsdate = rsdate + y + ";";
                        }
                    }
                }
                String dateqry[] = rsdate.split(";");
                if (dateqry.length > 1) {
                    query = query + " and ( GioRa like '%" + dateqry[0] + "%'  ";
                    for (int j = 0; j < dateqry.length; j++) {
                        query = query + " or GioRa like '%" + dateqry[j] + "%'  ";
                    }
                    query = query + " ) ";
                } else {
                    query = query + " and  GioRa like '%" + dateqry[0] + "%'  ";
                }
            }
            query = query + " group by A.MaBan ";
            query = query + " Order by sum(A.TongCong) desc ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.HoaDon hd = new Beans.HoaDon();
                hd.setMaBan(rss.getInt(1));
                hd.setTongCong(rss.getDouble(2));
                v.add(hd);
            }
            stmt.close();
            rss.close();

            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static Vector Turnoverareagr(String ngaybd, String ngaykt, String makhuvuc, String tenban, int langid, String soluong) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select MaKV,  sum(A.TongCong)  "
                    + " from hoadon A, ban B "
                    + " where A.MaBan = B.MaBan ";

            if (!ngaybd.equals("")) {
                String rsdate = "";
                SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
                Calendar c1 = Calendar.getInstance();
                Calendar c2 = Calendar.getInstance();
                Calendar c3 = Calendar.getInstance();
                Date date1 = fm.parse(ngaybd);
                Date date2 = fm.parse(ngaykt);
                c1.setTime(date1);
                c2.setTime(date2);
                String query1 = " select GioRa from hoadon ";
                rss = stmt.executeQuery(query1);
                while (rss.next()) {
                    String x = rss.getString(1);
                    String ngaygio[] = x.split(" ");
                    String y = ngaygio[0];
                    Date date3 = fm.parse(y);
                    c3.setTime(date3);
                    if (c1.before(c2)) {
                        if (c1.before(c3) && c2.after(c3)) {
                            rsdate = rsdate + y + ";";
                        }
                        if (c3.equals(c1)) {
                            rsdate = rsdate + y + ";";
                        }
                        if (c3.equals(c2)) {
                            rsdate = rsdate + y + ";";
                        }
                    }
                    if (c1.equals(c2)) {
                        if (c3.equals(c1)) {
                            rsdate = rsdate + y + ";";
                        }
                    }
                }
                String dateqry[] = rsdate.split(";");
                if (dateqry.length > 1) {
                    query = query + " and ( GioRa like '%" + dateqry[0] + "%'  ";
                    for (int j = 0; j < dateqry.length; j++) {
                        query = query + " or GioRa like '%" + dateqry[j] + "%'  ";
                    }
                    query = query + " ) ";
                } else {
                    query = query + " and  GioRa like '%" + dateqry[0] + "%'  ";
                }
            }
            query = query + " group by MaKV ";
            query = query + " Order by sum(A.TongCong) desc ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.Ban b = new Beans.Ban();
                b.setMaKV(rss.getInt(1));
                v.add(b);
            }
            stmt.close();
            rss.close();

            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static double DoanhThuBan(String ngaybd, String ngaykt, String makhuvuc, String tenban, int langid, String soluong) {
        double doanhthu = 0;
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select sum(A.TongCong)  "
                    + " from hoadon A, ban B "
                    + " where A.MaBan = B.MaBan ";
            String[] arr = {makhuvuc, tenban};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(makhuvuc)) {
                    query = query + " and MaKV = " + arr[i] + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tenban)) {
                    query = query + " and TenBan = " + arr[i] + " ";
                }
            }
            if (!ngaybd.equals("")) {
                String rsdate = "";
                SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
                Calendar c1 = Calendar.getInstance();
                Calendar c2 = Calendar.getInstance();
                Calendar c3 = Calendar.getInstance();
                Date date1 = fm.parse(ngaybd);
                Date date2 = fm.parse(ngaykt);
                c1.setTime(date1);
                c2.setTime(date2);
                String query1 = " select GioRa from hoadon ";
                rss = stmt.executeQuery(query1);
                while (rss.next()) {
                    String x = rss.getString(1);
                    String ngaygio[] = x.split(" ");
                    String y = ngaygio[0];
                    Date date3 = fm.parse(y);
                    c3.setTime(date3);
                    if (c1.before(c2)) {
                        if (c1.before(c3) && c2.after(c3)) {
                            rsdate = rsdate + y + ";";
                        }
                        if (c3.equals(c1)) {
                            rsdate = rsdate + y + ";";
                        }
                        if (c3.equals(c2)) {
                            rsdate = rsdate + y + ";";
                        }
                    }
                    if (c1.equals(c2)) {
                        if (c3.equals(c1)) {
                            rsdate = rsdate + y + ";";
                        }
                    }
                }
                String dateqry[] = rsdate.split(";");
                if (dateqry.length > 1) {
                    query = query + " and ( GioRa like '%" + dateqry[0] + "%'  ";
                    for (int j = 0; j < dateqry.length; j++) {
                        query = query + " or GioRa like '%" + dateqry[j] + "%'  ";
                    }
                    query = query + " ) ";
                } else {
                    query = query + " and  GioRa like '%" + dateqry[0] + "%'  ";
                }
            }
            if (!soluong.equals("")) {
                query = query + "limit " + soluong;
            }

            rss = stmt.executeQuery(query);
            while (rss.next()) {
                doanhthu = rss.getDouble(1);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return doanhthu;
    }

    public static Vector Turnoverbill(String ngaybd, String ngaykt, String makhuvuc, String tenban, int langid, String soluong) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select A.SoHD, A.MaBan, GioRa,  sum(A.TongCong)  "
                    + " from hoadon A, ban B "
                    + " where A.MaBan = B.MaBan ";
            String[] arr = {makhuvuc, tenban};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(makhuvuc)) {
                    query = query + " and MaKV = " + arr[i] + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tenban)) {
                    query = query + " and TenBan = " + arr[i] + " ";
                }
            }
            if (!ngaybd.equals("")) {
                String rsdate = "";
                SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
                Calendar c1 = Calendar.getInstance();
                Calendar c2 = Calendar.getInstance();
                Calendar c3 = Calendar.getInstance();
                Date date1 = fm.parse(ngaybd);
                Date date2 = fm.parse(ngaykt);
                c1.setTime(date1);
                c2.setTime(date2);
                String query1 = " select GioRa from hoadon ";
                rss = stmt.executeQuery(query1);
                while (rss.next()) {
                    String x = rss.getString(1);
                    String ngaygio[] = x.split(" ");
                    String y = ngaygio[0];
                    Date date3 = fm.parse(y);
                    c3.setTime(date3);
                    if (c1.before(c2)) {
                        if (c1.before(c3) && c2.after(c3)) {
                            rsdate = rsdate + y + ";";
                        }
                        if (c3.equals(c1)) {
                            rsdate = rsdate + y + ";";
                        }
                        if (c3.equals(c2)) {
                            rsdate = rsdate + y + ";";
                        }
                    }
                    if (c1.equals(c2)) {
                        if (c3.equals(c1)) {
                            rsdate = rsdate + y + ";";
                        }
                    }
                }
                String dateqry[] = rsdate.split(";");
                if (dateqry.length > 1) {
                    query = query + " and ( GioRa like '%" + dateqry[0] + "%'  ";
                    for (int j = 0; j < dateqry.length; j++) {
                        query = query + " or GioRa like '%" + dateqry[j] + "%'  ";
                    }
                    query = query + " ) ";
                } else {
                    query = query + " and  GioRa like '%" + dateqry[0] + "%'  ";
                }
            }
            query = query + " group by A.SoHD ";
            query = query + " Order by sum(A.TongCong) desc ";
            if (!soluong.equals("")) {
                query = query + "limit " + soluong;
            }
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.HoaDon hd = new Beans.HoaDon();
                hd.setSoHD(rss.getInt(1));
                hd.setMaBan(rss.getInt(2));
                hd.setGioRa(rss.getString(3));
                hd.setTongCong(rss.getDouble(4));
                v.add(hd);
            }
            stmt.close();
            rss.close();

            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static double DoanhThuHD(String ngaybd, String ngaykt, String makhuvuc, String tenban, int langid, String soluong) {
        double doanhthu = 0;
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select  sum(A.TongCong)  "
                    + " from hoadon A, ban B "
                    + " where A.MaBan = B.MaBan ";
            String[] arr = {makhuvuc, tenban};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(makhuvuc)) {
                    query = query + " and MaKV = " + arr[i] + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tenban)) {
                    query = query + " and TenBan = " + arr[i] + " ";
                }
            }
            if (!ngaybd.equals("")) {
                String rsdate = "";
                SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
                Calendar c1 = Calendar.getInstance();
                Calendar c2 = Calendar.getInstance();
                Calendar c3 = Calendar.getInstance();
                Date date1 = fm.parse(ngaybd);
                Date date2 = fm.parse(ngaykt);
                c1.setTime(date1);
                c2.setTime(date2);
                String query1 = " select GioRa from hoadon ";
                rss = stmt.executeQuery(query1);
                while (rss.next()) {
                    String x = rss.getString(1);
                    String ngaygio[] = x.split(" ");
                    String y = ngaygio[0];
                    Date date3 = fm.parse(y);
                    c3.setTime(date3);
                    if (c1.before(c2)) {
                        if (c1.before(c3) && c2.after(c3)) {
                            rsdate = rsdate + y + ";";
                        }
                        if (c3.equals(c1)) {
                            rsdate = rsdate + y + ";";
                        }
                        if (c3.equals(c2)) {
                            rsdate = rsdate + y + ";";
                        }
                    }
                    if (c1.equals(c2)) {
                        if (c3.equals(c1)) {
                            rsdate = rsdate + y + ";";
                        }
                    }
                }
                String dateqry[] = rsdate.split(";");
                if (dateqry.length > 1) {
                    query = query + " and ( GioRa like '%" + dateqry[0] + "%'  ";
                    for (int j = 0; j < dateqry.length; j++) {
                        query = query + " or GioRa like '%" + dateqry[j] + "%'  ";
                    }
                    query = query + " ) ";
                } else {
                    query = query + " and  GioRa like '%" + dateqry[0] + "%'  ";
                }
            }
            if (!soluong.equals("")) {
                query = query + "limit " + soluong;
            }

            rss = stmt.executeQuery(query);
            while (rss.next()) {
                doanhthu = rss.getDouble(1);
            }
            stmt.close();
            rss.close();

            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return doanhthu;
    }

    public static Vector Topfood(String ngaybd, String ngaykt, String soluong, int langid) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;

        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select A.MaMA, sum(A.SoLuong) as soluong, sum(A.ThanhTien), A.Gia  "
                    + " from chitiethd A, hoadon B, monan C "
                    + " where A.SoHD = B.SoHD and A.MaMA = C.MaMA and C.MaNN = " + langid + " ";
            if (!ngaybd.equals("")) {
                String rsdate = "";
                SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
                Calendar c1 = Calendar.getInstance();
                Calendar c2 = Calendar.getInstance();
                Calendar c3 = Calendar.getInstance();
                Date date1 = fm.parse(ngaybd);
                Date date2 = fm.parse(ngaykt);
                c1.setTime(date1);
                c2.setTime(date2);
                String query1 = " select GioRa from hoadon ";
                rss = stmt.executeQuery(query1);
                while (rss.next()) {
                    String x = rss.getString(1);
                    String ngaygio[] = x.split(" ");
                    String y = ngaygio[0];
                    Date date3 = fm.parse(y);
                    c3.setTime(date3);
                    if (c1.before(c2)) {
                        if (c1.before(c3) && c2.after(c3)) {
                            rsdate = rsdate + y + ";";
                        }
                        if (c3.equals(c1)) {
                            rsdate = rsdate + y + ";";
                        }
                        if (c3.equals(c2)) {
                            rsdate = rsdate + y + ";";
                        }
                    }
                    if (c1.equals(c2)) {
                        if (c3.equals(c1)) {
                            rsdate = rsdate + y + ";";
                        }
                    }
                }
                String dateqry[] = rsdate.split(";");
                if (dateqry.length > 1) {
                    query = query + " and ( GioRa like '%" + dateqry[0] + "%'  ";
                    for (int j = 0; j < dateqry.length; j++) {
                        query = query + " or GioRa like '%" + dateqry[j] + "%'  ";
                    }
                    query = query + " ) ";
                } else {
                    query = query + " and  GioRa like '%" + dateqry[0] + "%'  ";
                }
            }
            query = query + " group by A.MaMA Order by soluong desc ";
            if (!soluong.equals("")) {
                query = query + " limit " + soluong + " ";
            }
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.CTHoaDon cthd = new Beans.CTHoaDon();
                cthd.setMaMA(rss.getInt(1));
                cthd.setSoLuong(rss.getInt(2));
                cthd.setThanhTien(rss.getDouble(3));
                cthd.setGia(rss.getDouble(4));
                v.add(cthd);
            }
            stmt.close();
            rss.close();

            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static Vector Topfoodgroup(String ngaybd, String ngaykt, int langid, int MaNMA) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;

        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select A.MaMA, sum(A.SoLuong) as soluong, sum(A.ThanhTien), A.Gia  "
                    + " from chitiethd A, hoadon B, monan C "
                    + " where A.SoHD = B.SoHD and A.MaMA = C.MaMA and C.MaNN = " + langid + " and MaNMA = " + MaNMA + " ";
            if (!ngaybd.equals("")) {
                String rsdate = "";
                SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
                Calendar c1 = Calendar.getInstance();
                Calendar c2 = Calendar.getInstance();
                Calendar c3 = Calendar.getInstance();
                Date date1 = fm.parse(ngaybd);
                Date date2 = fm.parse(ngaykt);
                c1.setTime(date1);
                c2.setTime(date2);
                String query1 = " select GioRa from hoadon ";
                rss = stmt.executeQuery(query1);
                while (rss.next()) {
                    String x = rss.getString(1);
                    String ngaygio[] = x.split(" ");
                    String y = ngaygio[0];
                    Date date3 = fm.parse(y);
                    c3.setTime(date3);
                    if (c1.before(c2)) {
                        if (c1.before(c3) && c2.after(c3)) {
                            rsdate = rsdate + y + ";";
                        }
                        if (c3.equals(c1)) {
                            rsdate = rsdate + y + ";";
                        }
                        if (c3.equals(c2)) {
                            rsdate = rsdate + y + ";";
                        }
                    }
                    if (c1.equals(c2)) {
                        if (c3.equals(c1)) {
                            rsdate = rsdate + y + ";";
                        }
                    }
                }
                String dateqry[] = rsdate.split(";");
                if (dateqry.length > 1) {
                    query = query + " and ( GioRa like '%" + dateqry[0] + "%'  ";
                    for (int j = 0; j < dateqry.length; j++) {
                        query = query + " or GioRa like '%" + dateqry[j] + "%'  ";
                    }
                    query = query + " ) ";
                } else {
                    query = query + " and  GioRa like '%" + dateqry[0] + "%'  ";
                }
            }
            query = query + " group by A.MaMA Order by soluong desc ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.CTHoaDon cthd = new Beans.CTHoaDon();
                cthd.setMaMA(rss.getInt(1));
                cthd.setSoLuong(rss.getInt(2));
                cthd.setThanhTien(rss.getDouble(3));
                cthd.setGia(rss.getDouble(4));
                v.add(cthd);
            }
            stmt.close();
            rss.close();

            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static Vector Topfoodgr(String ngaybd, String ngaykt, String soluong, int langid) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select MaNMA, sum(A.SoLuong) as soluong "
                    + " from chitiethd A, hoadon B, monan C "
                    + " where A.SoHD = B.SoHD and A.MaMA = C.MaMA and C.MaNN = " + langid + " ";
            if (!ngaybd.equals("")) {
                String rsdate = "";
                SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
                Calendar c1 = Calendar.getInstance();
                Calendar c2 = Calendar.getInstance();
                Calendar c3 = Calendar.getInstance();
                Date date1 = fm.parse(ngaybd);
                Date date2 = fm.parse(ngaykt);
                c1.setTime(date1);
                c2.setTime(date2);
                String query1 = " select GioRa from hoadon ";
                rss = stmt.executeQuery(query1);
                while (rss.next()) {
                    String x = rss.getString(1);
                    String ngaygio[] = x.split(" ");
                    String y = ngaygio[0];
                    Date date3 = fm.parse(y);
                    c3.setTime(date3);
                    if (c1.before(c2)) {
                        if (c1.before(c3) && c2.after(c3)) {
                            rsdate = rsdate + y + ";";
                        }
                        if (c3.equals(c1)) {
                            rsdate = rsdate + y + ";";
                        }
                        if (c3.equals(c2)) {
                            rsdate = rsdate + y + ";";
                        }
                    }
                    if (c1.equals(c2)) {
                        if (c3.equals(c1)) {
                            rsdate = rsdate + y + ";";
                        }
                    }
                }
                String dateqry[] = rsdate.split(";");
                if (dateqry.length > 1) {
                    query = query + " and ( GioRa like '%" + dateqry[0] + "%'  ";
                    for (int j = 0; j < dateqry.length; j++) {
                        query = query + " or GioRa like '%" + dateqry[j] + "%'  ";
                    }
                    query = query + " ) ";
                } else {
                    query = query + " and  GioRa like '%" + dateqry[0] + "%'  ";
                }
            }
            query = query + " group by MaNMA Order by soluong desc ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.MonAn ma = new Beans.MonAn();
                ma.setMaNMA(rss.getInt(1));
                v.add(ma);
            }
            stmt.close();
            rss.close();

            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static Vector Toptable(String ngaybd, String ngaykt, String soluong, int langid) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select A.MaBan,  count(SoHD) as soluong  "
                    + " from hoadon A, ban B "
                    + " where A.MaBan = B.MaBan ";
            if (!ngaybd.equals("")) {
                String rsdate = "";
                SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
                Calendar c1 = Calendar.getInstance();
                Calendar c2 = Calendar.getInstance();
                Calendar c3 = Calendar.getInstance();
                Date date1 = fm.parse(ngaybd);
                Date date2 = fm.parse(ngaykt);
                c1.setTime(date1);
                c2.setTime(date2);
                String query1 = " select GioRa from hoadon ";
                rss = stmt.executeQuery(query1);
                while (rss.next()) {
                    String x = rss.getString(1);
                    String ngaygio[] = x.split(" ");
                    String y = ngaygio[0];
                    Date date3 = fm.parse(y);
                    c3.setTime(date3);
                    if (c1.before(c2)) {
                        if (c1.before(c3) && c2.after(c3)) {
                            rsdate = rsdate + y + ";";
                        }
                        if (c3.equals(c1)) {
                            rsdate = rsdate + y + ";";
                        }
                        if (c3.equals(c2)) {
                            rsdate = rsdate + y + ";";
                        }
                    }
                    if (c1.equals(c2)) {
                        if (c3.equals(c1)) {
                            rsdate = rsdate + y + ";";
                        }
                    }
                }
                String dateqry[] = rsdate.split(";");
                if (dateqry.length > 1) {
                    query = query + " and ( GioRa like '%" + dateqry[0] + "%'  ";
                    for (int j = 0; j < dateqry.length; j++) {
                        query = query + " or GioRa like '%" + dateqry[j] + "%'  ";
                    }
                    query = query + " ) ";
                } else {
                    query = query + " and  GioRa like '%" + dateqry[0] + "%'  ";
                }
            }
            query = query + " group by A.MaBan Order by soluong desc ";
            if (!soluong.equals("")) {
                query = query + " limit " + soluong;
            }
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.HoaDon hd = new Beans.HoaDon();
                hd.setMaBan(rss.getInt(1));
                hd.setSoHD(rss.getInt(2));
                v.add(hd);
            }
            stmt.close();
            rss.close();

            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static Vector Toptablegr(String ngaybd, String ngaykt, String soluong, int langid) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select MaKV,  count(SoHD) as soluong  "
                    + " from hoadon A, ban B "
                    + " where A.MaBan = B.MaBan ";
            if (!ngaybd.equals("")) {
                String rsdate = "";
                SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
                Calendar c1 = Calendar.getInstance();
                Calendar c2 = Calendar.getInstance();
                Calendar c3 = Calendar.getInstance();
                Date date1 = fm.parse(ngaybd);
                Date date2 = fm.parse(ngaykt);
                c1.setTime(date1);
                c2.setTime(date2);
                String query1 = " select GioRa from hoadon ";
                rss = stmt.executeQuery(query1);
                while (rss.next()) {
                    String x = rss.getString(1);
                    String ngaygio[] = x.split(" ");
                    String y = ngaygio[0];
                    Date date3 = fm.parse(y);
                    c3.setTime(date3);
                    if (c1.before(c2)) {
                        if (c1.before(c3) && c2.after(c3)) {
                            rsdate = rsdate + y + ";";
                        }
                        if (c3.equals(c1)) {
                            rsdate = rsdate + y + ";";
                        }
                        if (c3.equals(c2)) {
                            rsdate = rsdate + y + ";";
                        }
                    }
                    if (c1.equals(c2)) {
                        if (c3.equals(c1)) {
                            rsdate = rsdate + y + ";";
                        }
                    }
                }
                String dateqry[] = rsdate.split(";");
                if (dateqry.length > 1) {
                    query = query + " and ( GioRa like '%" + dateqry[0] + "%'  ";
                    for (int j = 0; j < dateqry.length; j++) {
                        query = query + " or GioRa like '%" + dateqry[j] + "%'  ";
                    }
                    query = query + " ) ";
                } else {
                    query = query + " and  GioRa like '%" + dateqry[0] + "%'  ";
                }
            }
            query = query + " group by MaKV Order by soluong desc ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.Ban b = new Beans.Ban();
                b.setMaKV(rss.getInt(1));
                v.add(b);
            }
            stmt.close();
            rss.close();

            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static Vector Toptablegroup(String ngaybd, String ngaykt, int langid, int MaKV) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select A.MaBan,  count(SoHD) as soluong  "
                    + " from hoadon A, ban B "
                    + " where A.MaBan = B.MaBan and MaKV = " + MaKV + " ";
            if (!ngaybd.equals("")) {
                String rsdate = "";
                SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
                Calendar c1 = Calendar.getInstance();
                Calendar c2 = Calendar.getInstance();
                Calendar c3 = Calendar.getInstance();
                Date date1 = fm.parse(ngaybd);
                Date date2 = fm.parse(ngaykt);
                c1.setTime(date1);
                c2.setTime(date2);
                String query1 = " select GioRa from hoadon ";
                rss = stmt.executeQuery(query1);
                while (rss.next()) {
                    String x = rss.getString(1);
                    String ngaygio[] = x.split(" ");
                    String y = ngaygio[0];
                    Date date3 = fm.parse(y);
                    c3.setTime(date3);
                    if (c1.before(c2)) {
                        if (c1.before(c3) && c2.after(c3)) {
                            rsdate = rsdate + y + ";";
                        }
                        if (c3.equals(c1)) {
                            rsdate = rsdate + y + ";";
                        }
                        if (c3.equals(c2)) {
                            rsdate = rsdate + y + ";";
                        }
                    }
                    if (c1.equals(c2)) {
                        if (c3.equals(c1)) {
                            rsdate = rsdate + y + ";";
                        }
                    }
                }
                String dateqry[] = rsdate.split(";");
                if (dateqry.length > 1) {
                    query = query + " and ( GioRa like '%" + dateqry[0] + "%'  ";
                    for (int j = 0; j < dateqry.length; j++) {
                        query = query + " or GioRa like '%" + dateqry[j] + "%'  ";
                    }
                    query = query + " ) ";
                } else {
                    query = query + " and  GioRa like '%" + dateqry[0] + "%'  ";
                }
            }
            query = query + " group by A.MaBan Order by soluong desc ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.HoaDon hd = new Beans.HoaDon();
                hd.setMaBan(rss.getInt(1));
                hd.setSoHD(rss.getInt(2));
                v.add(hd);
            }
            stmt.close();
            rss.close();

            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static double ThongKeTheoThang(int month, int year) {

        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        double tongtien = 0;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select sum(TongCong)  "
                    + " from  hoadon "
                    + " where GioRa like '%" + month + "/" + year + "%' ";
            String kyhieu = TienTe_Model.LayKyHieuTT(1);
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                if (kyhieu.equals("VND")) {
                    tongtien = PhieuDatMA_Model.Round(rss.getDouble(1) / 1000, 0);
                } else {
                    tongtien = PhieuDatMA_Model.Round(rss.getDouble(1), 2);
                }
            }
            stmt.close();
            rss.close();

            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return tongtien;
    }

    public static double ChartFoodGroup(int manhomma, int langid) {

        Connection conn = null;
        ResultSet rss = null;
        ResultSet rss1 = null;
        double tongtien = 0;
        double tong = 0;
        double x = 0;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select sum(A.ThanhTien)  "
                    + " from chitiethd A, monan C "
                    + " where A.MaMA = C.MaMA and C.MaNMA = " + manhomma + " and C.MaNN = " + langid + " ";
            String query1 = " select sum(A.ThanhTien)  "
                    + " from chitiethd A, monan C "
                    + " where A.MaMA = C.MaMA and C.MaNN = " + langid + " ";
            rss = stmt.executeQuery(query1);
            while (rss.next()) {
                tong = rss.getDouble(1);
            }
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                x = rss.getDouble(1);
                tongtien = (rss.getDouble(1) / tong) * 100;
                tongtien = PhieuDatMA_Model.Round(tongtien, 2);
            }
            stmt.close();
            rss.close();
            rss1.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (rss1 != null) {
                try {
                    rss1.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return tongtien;
    }

    public static double ChartArea(int makv, int langid) {

        Connection conn = null;
        ResultSet rss = null;
        ResultSet rss1 = null;
        double tongtien = 0;
        double tong = 0;
        double x = 0;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select sum(A.TongCong)  "
                    + " from hoadon A, ban B "
                    + " where A.MaBan = B.MaBan and MaKV = " + makv + " ";
            String query1 = " select sum(A.TongCong)  "
                    + " from hoadon A, ban B "
                    + " where A.MaBan = B.MaBan ";
            rss = stmt.executeQuery(query1);
            while (rss.next()) {
                tong = rss.getDouble(1);
            }
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                x = rss.getDouble(1);
                tongtien = (rss.getDouble(1) / tong) * 100;
                tongtien = PhieuDatMA_Model.Round(tongtien, 2);
            }
            stmt.close();
            rss.close();
            rss1.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (rss1 != null) {
                try {
                    rss1.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return tongtien;
    }
}
